﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using Mpi.Common;
using Mpi.Common.Collections;
using Xeml.Document;
using Xeml.Sampling;
using Xeml.Sampling.Contracts;
using System.ComponentModel;

namespace XemlDataProvider
{
    public class EnzymeLabDataProvider : IDataProvider
    {
        MyConfig _config;

        public Xeml.SQLDataProviders.DataProviderConfiguration Configuration
        {
            get { return null; }
        }

        /// <summary>
        /// The standard constructor
        /// </summary>
        public EnzymeLabDataProvider()
        {
            _config = new MyConfig
            {
                Server = "2003dellp285001",
                Database = "EnzymeLaborDB",
                UseWinAuthentication = true
            };

            //Hier Properties anlegen die benötigt werden um einen Connectionstring zu bauen.
            //CustomProperty<string> server = new CustomProperty<string>("2003dellp285001", new string[] { "2003dellp285001" }, false);
            //CustomProperty<string> database = new CustomProperty<string>("EnzymeLaborDB", new string[] { "EnzymeLaborDB" }, false);
            //CustomProperty<string> user = new CustomProperty<string>("lab", null, false);
            //CustomProperty<string> passwd = new CustomProperty<string>("enzyme", null, false);
            //CustomProperty<bool> WinAuth = new CustomProperty<bool>(false, null, false);

            FriendlyName = "DasIstEinFriendlyName";
        }

        #region Helper methods



        /// <summary>
        /// Requests an authentication for the connection.
        /// </summary>
        /// <returns></returns>
        private AuthenticationRequestEventArgs CheckAuthentication()
        {



            //string user = "";
            //string pwd = "";
            //if (_connString.Contains("$user") || _connString.Contains("$passwd"))
            //{
            //    if (!_connString.Contains("$user"))
            //    {
            //        string[] temp = _connString.Split(";".ToCharArray());
            //        foreach (string s in temp)
            //        {
            //            if (s.ToLower().Contains("User Id".ToLower()))
            //            {
            //                string[] temp2 = s.Split('=');
            //                if (temp2.Length > 1)
            //                    user = temp2[1];
            //            }
            //        }
            //    }

            //    if (!_connString.Contains("$passwd"))
            //    {
            //        string[] temp = _connString.Split(";".ToCharArray());
            //        foreach (string s in temp)
            //        {
            //            if (s.ToLower().Contains("PWD".ToLower()))
            //            {
            //                string[] temp2 = s.Split('=');
            //                if (temp2.Length > 1)
            //                    pwd = temp2[1];
            //            }
            //        }
            //    }
            //    AuthenticationRequestEventArgs args = new AuthenticationRequestEventArgs(user, pwd);
            //    OnAuthenticationRequest(args); //Authentication request event senden
            //    return args;
            //}
            return new AuthenticationRequestEventArgs("enzyme", "lab");
        }

        //optional die ToString() Methode überschreiben.
        public override string ToString()
        {
            return this.ComponentName;
        }


        /// <summary>
        /// Builds a sql connection from the current config and authentication method
        /// </summary>
        /// <param name="userCancel">Indicates whether or not the user canceled the authentication process</param>
        /// <returns>The resulting sql connection.</returns>
        private SqlConnection BuildConnection(out bool userCancel)
        {
            userCancel = false;
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = _config.Server; //set the server from the current config
            sb.InitialCatalog = _config.Database; //set the database from the current config
            sb.IntegratedSecurity = _config.UseWinAuthentication; //set authentication method

            if (!sb.IntegratedSecurity) // if not win auth is used then we have to ask the user for credentials
            {
                AuthenticationRequestEventArgs ar = new AuthenticationRequestEventArgs(); //a auth reequest object bubbling up to the user via event
                ar.UserName = "lab"; //set a default user
                ar.Password = "enzyme"; // set a default passwd
                OnAuthenticationRequest(ar); //The request argument is send to the user 
                sb.UserID = ar.UserName; // Set username came backfrom the user
                sb.Password = ar.Password; // set the password came back from the user
                userCancel = ar.Cancel; // if user cancel then it goes out via userCancel parameter

            }

            SqlConnection conn = new SqlConnection(sb.ConnectionString);
            return conn;

        }

        protected virtual void OnAuthenticationRequest(AuthenticationRequestEventArgs args)
        {
            if (AuthenticationRequest != null)
                AuthenticationRequest(this, args);
        }




        #endregion

        #region IDataProvider Members

        /// <summary>
        /// An optional logo for the provider
        /// </summary>
        public System.Drawing.Image Logo
        {
            get { return EnzymeLabLib.Resource.ELTON; }
        }





        /// <summary>
        /// A collection of properties are required to customize the compilation
        /// </summary>
        public MyConfig ConfiguartionObject
        {
            get { return _config; }
        }




        /// <summary>
        /// Indicates whether or not the provider supports automapping of samples.
        /// </summary>
        public bool SupportsAutoMapping
        {
            get { return true; }
        }





        ///// <summary>
        ///// List all available analytes of this provider.
        ///// </summary>
        ///// <returns>A list of analyte identifiere.</returns>
        //public IEnumerable<string> QueryAnalytes()
        //{
        //    AuthenticationRequestEventArgs area = CheckAuthentication();
        //    string workingConnstring = BuildWorkingConnectionString(area.UserName, area.Password);
        //    List<string> temp = new List<string>();

        //    using (SqlConnection sqlconn = new SqlConnection(workingConnstring))
        //    {
        //        SqlCommand cmd = new SqlCommand();
        //        cmd.CommandType = System.Data.CommandType.Text;
        //        cmd.Connection = sqlconn;
        //        cmd.CommandText = "select Enzyme from dbo.EnzymeCodes";

        //        sqlconn.Open();

        //        try
        //        {
        //            SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);


        //            while (reader.Read())
        //            {
        //                temp.Add(reader.GetString(0));
        //            }
        //        }
        //        finally
        //        {
        //            sqlconn.Close();
        //        }
        //    }

        //    return temp.ToArray();

        //}

        /// <summary>
        /// Returns a list of keys to identify samples within the data source
        /// </summary>
        /// <returns>List of key names</returns>
        public IEnumerable<string> ListForeignKey()
        {
            //Hier ein Liste von keys zurückgeben, die benötigt werden um Samples zu identifizieren.
            return new string[] { "Experiment", "Sample" };
        }

        /// <summary>
        /// Returns a list of possible values to a given key
        /// </summary>
        /// <param name="key">The targrt key</param>
        /// <param name="filter">A filter expression to restrict the result</param>
        /// <returns>A lis of possible values</returns>
        public IEnumerable<string> FilterKeyValues(string key, string filter)
        {
            //Die Methode wird für den Fall benötigt, dass der User ein mauelles mapping herstellen muß.
            int sample = -1;
            string experiment = "";

            List<string> ret = new List<string>();

            if (key == "Experiment")
            {
                if (filter.StartsWith("Sample="))
                {
                    var split = filter.Split("=".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
                    if (split.Length >= 2)
                    {

                        Int32.TryParse(split[1], out sample);

                    }
                }
            }
            else if (key == "Sample")
            {
                if (filter.StartsWith("Experiment="))
                {
                    var split = filter.Split("=".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
                    if (split.Length >= 2)
                    {

                        experiment = split[1];

                    }
                    else
                        return ret;
                }
                else
                    return ret;
            }


            bool cancel;
            using (SqlConnection sqlconn = BuildConnection(out cancel))
            {
                if (!cancel)
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Connection = sqlconn;
                    cmd.CommandText = "[dbo].[GetExperimentOrSample]";
                    cmd.Parameters.Add("@experiment", System.Data.SqlDbType.NVarChar);
                    cmd.Parameters.Add("@sample", System.Data.SqlDbType.Int);
                    sqlconn.Open();
                    try
                    {
                        cmd.Parameters["@experiment"].Value = experiment;
                        cmd.Parameters["@sample"].Value = sample;
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                ret.Add(reader.GetValue(0).ToString());
                            }
                            reader.Close();
                        }

                    }
                    finally
                    {
                        sqlconn.Close();
                    }
                }
            }
            return ret;
        }

        /// <summary>
        /// Maps samples automatically
        /// </summary>
        /// <param name="sidmappings">The samples to map</param>
        /// <returns>Indicates whether or not the mapping was successfull</returns>
        public void AutoMap(Guid expId, string expName, IEnumerable<Xeml.Document.SidMapping> sidmappings)
        {

            bool cancel;
            using (SqlConnection sqlconn = BuildConnection(out cancel))
            {
                if (!cancel)
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Connection = sqlconn;
                    cmd.CommandText = "select * from [dbo].[FreshWeights] where Experiment_Fk = @experiment and Sample = @sample";
                    cmd.Parameters.Add("@experiment", System.Data.SqlDbType.NVarChar);
                    cmd.Parameters.Add("@sample", System.Data.SqlDbType.Int);
                    sqlconn.Open();
                    try
                    {
                        foreach (var item in sidmappings)
                        {
                            item.ForeignKeyMap.Clear();
                            cmd.Parameters["@experiment"].Value = expName;
                            cmd.Parameters["@sample"].Value = item.SampleId;
                            using (SqlDataReader reader = cmd.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
                                    item.ForeignKeyMap.Add("Experiment", expName);
                                    item.ForeignKeyMap.Add("Sample", item.SampleId.ToString());
                                }
                                reader.Close();
                            }
                        }
                    }
                    finally
                    {
                        sqlconn.Close();
                    }
                }
            }
        }

        /// <summary>
        /// Validates the existence of an sample.
        /// </summary>
        /// <param name="sim">The sample to validate.</param>
        /// <returns>An value indicting whether the sample exist or not.</returns>
        public SidLookUpResult Validate(Xeml.Document.SidMapping sim)
        {

            if (!sim.ForeignKeyMap.ContainsKey("Experiment") || !sim.ForeignKeyMap.ContainsKey("Sample"))
                return SidLookUpResult.UnsupportedMapping;

            int dummy;
            if (!Int32.TryParse(sim.ForeignKeyMap["Sample"], out dummy))
                return SidLookUpResult.UnsupportedMapping;



            AuthenticationRequestEventArgs args = CheckAuthentication();
            if (args.Cancel)
                return SidLookUpResult.UnknownSample;
            //string workingConnstring = BuildWorkingConnectionString(args.UserName, args.Password);

            using (SqlConnection sqlconn = new SqlConnection())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Connection = sqlconn;
                cmd.CommandText = "select * from [dbo].[FreshWeights] where Experiment_Fk = @experiment and Sample = @sample";
                cmd.Parameters.Add("@experiment", System.Data.SqlDbType.NVarChar);
                cmd.Parameters.Add("@sample", System.Data.SqlDbType.Int);
                sqlconn.Open();
                try
                {

                    SidLookUpResult result = SidLookUpResult.UnknownSample;

                    cmd.Parameters["@experiment"].Value = sim.ForeignKeyMap["Experiment"];
                    cmd.Parameters["@sample"].Value = Int32.Parse(sim.ForeignKeyMap["Sample"]);
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {

                        if (reader.HasRows)
                        {
                            result = SidLookUpResult.Valid;
                        }
                        else
                        {
                            result = SidLookUpResult.UnknownSample;
                        }
                        reader.Close();

                    }
                    return result;
                }

                finally
                {
                    sqlconn.Close();
                }
            }

        }

        ///// <summary>
        ///// Tests the connection to the data source.
        ///// </summary>
        ///// <returns>A value which indicates the success of the test.</returns>
        //public ConnectResult TestConnection()
        //{

        //    AuthenticationRequestEventArgs area = CheckAuthentication();
        //    //if (area != null && area.Cancel == true)
        //    //    return ConnectResult.Cancelled;
        //    //else
        //    //{

        //    //    return TestConnection(area.UserName, area.Password);
        //    //}
        //}


        ///<summary>
        ///Tests the connection to the data source with given user/password.
        ///</summary>
        ///<returns>A value which indicates the success of the test.</returns>
        public ConnectResult TestConnection(string user, string passwd)
        {

            string workingConnstring = "Todo";// = BuildWorkingConnectionString(user, passwd);

            SqlConnection con = new SqlConnection(workingConnstring);
            ConnectResult ret = ConnectResult.Failed;
            try
            {
                con.Open();
                ret = ConnectResult.Success;
            }
            catch (Exception)
            {
                ret = ConnectResult.Failed;
            }
            finally
            {
                con.Close();
                con = null;
            }

            return ret;
        }

        /// <summary>
        /// Collects the the sample data for specific list of sample identfiere. 
        /// </summary>
        /// <param name="samples">The list of sample identifiere.</param>
        public KeyKeyValueCollection<string, int, object> QueryData(List<SidMapping> samples)
        {
            KeyKeyValueCollection<string, int, object> ret = new KeyKeyValueCollection<string, int, object>();

            //var experiments = (from sid in samples
            //                   select sid.ForeignKeyMap["Experiment"]).Distinct();



            AuthenticationRequestEventArgs args = CheckAuthentication();
            string workingConnstring = "Todo";// BuildWorkingConnectionString(args.UserName, args.Password);

            using (SqlConnection sqlconn = new SqlConnection(workingConnstring))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Connection = sqlconn;
                cmd.CommandText = "ProcessedSampleData";
                cmd.Parameters.Add("@experiment", System.Data.SqlDbType.NVarChar);
                cmd.Parameters.Add("@sample", System.Data.SqlDbType.Int);
                sqlconn.Open();

                try
                {
                    foreach (var item in samples)
                    {
                        if (Validate(item) == SidLookUpResult.Valid)
                        {
                            cmd.Parameters["@experiment"].Value = item.ForeignKeyMap["Experiment"];
                            cmd.Parameters["@sample"].Value = Int32.Parse(item.ForeignKeyMap["Sample"]);
                            using (SqlDataReader reader = cmd.ExecuteReader())
                            {

                                while (reader.Read())
                                {
                                    string enzyme = reader.GetString(3);
                                    //int sample = reader.GetInt32(1);
                                    double val = reader.GetDouble(4);

                                    if (ret.Contains(enzyme, item.SampleId))
                                        ret[enzyme, item.SampleId] = val;
                                    else
                                        ret.Add(enzyme, item.SampleId, val);
                                }
                                reader.Close();
                            }
                        }
                    }
                }
                finally
                {
                    sqlconn.Close();
                }




            }

            return ret;
        }


        /// <summary>
        /// An event handler for requesting authentication information.
        /// </summary>
        public event EventHandler<Xeml.Sampling.AuthenticationRequestEventArgs> AuthenticationRequest;




        #endregion

        #region IXemlComponent Members

        private const string URI = "http://www.mpimp-golm.mpg.de/xeml/2007/enzymelab";
        /// <summary>
        /// The universal identifiere for this handler
        /// </summary>
        public string Uri
        {
            get { return URI; }
        }

        /// <summary>
        /// The name of the publisher of this component.
        /// </summary>
        public string Publisher
        {
            get { return "Max Planck Institute of Molecular Plant Physiology, Potsdam/Golm, Germany"; }
        }



        /// <summary>
        /// The version string for this component.
        /// </summary>
        private Version _version = new Version(1, 0, 0, 0);
        public Version Version
        {
            get { return _version; }
        }

        /// <summary>
        /// The name of the component.
        /// </summary>
        public string ComponentName
        {
            get { return "EnzymeLabProvider"; }
        }

        /// <summary>
        /// The author of the component.
        /// </summary>
        public string Author
        {
            get { return "Jan Hannemann"; }
        }

        /// <summary>
        /// A short description about the aim of the component.
        /// </summary>
        public string Description
        {
            get { return "A data provider for the highthroughput enzyme platform of the AG Stitt."; }
        }



        /// <summary>
        /// Creates a copy of the component
        /// </summary>
        /// <returns></returns>
        public Xeml.Document.Contracts.IXemlComponent Copy()
        {
            return new EnzymeLabDataProvider();
        }

        #endregion

        public string ConnectionString()
        {
            SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
            scsb.DataSource = _config.Server;
            scsb.InitialCatalog = _config.Database;
            scsb.IntegratedSecurity = _config.UseWinAuthentication;
            return scsb.ToString();
        }

        public void SetConnectionString(string value)
        {
            SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder(value);
            _config.Server = scsb.DataSource;
            _config.Database = scsb.InitialCatalog;
            _config.UseWinAuthentication = scsb.IntegratedSecurity;
        }

        public string FriendlyName { get; set; }

        #region IDataProvider Members
        public event EventHandler<XemlFilePathRequestEventArgs> XemlFilePathRequest;

        public System.ComponentModel.INotifyPropertyChanged ConfigurationObject
        {
            get { return null; }
        }

        public IEnumerable<string> FilterKeyValues(string key, Dictionary<string, string> filter)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<SidMapping> AutoMap(Guid expId, string expName, IEnumerable<int> samples)
        {
            throw new NotImplementedException();
        }

        public ConnectResult TestCredentials(string user, string password)
        {
            throw new NotImplementedException();
        }

        public System.Data.IDbConnection BuildConnection()
        {
            throw new NotImplementedException();
        }

        public void Configure(Xeml.SQLDataProviders.DataProviderConfiguration config)
        {
            throw new NotImplementedException();
        }
        #endregion


    }


    public class MyConfig : INotifyPropertyChanged
    {
        private string _server;
        public string Server
        {
            get
            {
                return _server;
            }
            set
            {
                if (_server != value)
                {
                    _server = value;
                    OnpropertyChanged("Server");
                }
            }
        }

        private string _db;
        public string Database
        {
            get
            {
                return _db;
            }
            set
            {
                if (_db != value)
                {
                    _db = value;
                    OnpropertyChanged("Database");
                }
            }
        }

        private bool _winauth;
        public bool UseWinAuthentication
        {
            get
            {
                return _winauth;
            }
            set
            {
                if (_winauth != value)
                {
                    _winauth = value;
                    OnpropertyChanged("UseWinAuthentication");
                }
            }
        }

        #region INotifyPropertyChanged Members

        private void OnpropertyChanged(string prop)
        {
            if (PropertyChanged != null)
                PropertyChanged(this, new PropertyChangedEventArgs(prop));
        }

        public event PropertyChangedEventHandler PropertyChanged;

        #endregion
    }
}
